/*
Create figure 2, which displays the percent of borrowers/defaulters/foreclosures
that have effective negative equity.
*/


/*******************************************************************************
** Bring in all the data
*******************************************************************************/

use "$DATA_OUT/NMDB_ASMB_respondents", clear
count // Notes of Figure 2 report the total number of respondents

* Set weights
svyset [pweight = analysis_weight]
* See https://www.ers.usda.gov/data-products/rural-urban-continuum-codes/.  
* 1 or 2 includes only counties in metro areas with 250,000+ residents.  
gen urban       = inlist(rucc,"01","02") 
gen one = 1


/*******************************************************************************
** Collapse the data with % underwater in various categories, and create one
**  dataset for each sample with the results
*******************************************************************************/


	
preserve
collapse  (mean) EUW* (sum) weights = one [pweight = analysis_weight]
gen category="All"
save "$DATA_OUT/processing/LTV_results_all", replace
restore

preserve
keep if in_default_sample==1
collapse  (mean) EUW* (sum) weights = one [pweight = analysis_weight]
gen category="Defaulters"
save "$DATA_OUT/processing/LTV_results_defaulters", replace
restore

preserve
keep if in_default_sample==1 & foreclosure==1
collapse  (mean) EUW* (sum) weights = one [pweight = analysis_weight]
gen category="Foreclosures"
save "$DATA_OUT/processing/LTV_results_foreclosures", replace


append using  "$DATA_OUT/processing/LTV_results_defaulters"
append using  "$DATA_OUT/processing/LTV_results_all"

sort category
order category


foreach var in EUW_LTV_FHFA_st EUW_LTV_FHFA_county EUW_LTV_FHFA_tract EUW_or_dis {
	replace `var' = 100*`var' //put things in percent
	
}




save "$DATA_OUT/processing/LTV_results_AllObs", replace
order category
restore


/**********************************************************************************************
Finally, produce the figures for the paper
**********************************************************************************************/

** Bring in the data on % EUW in each category
use "$DATA_OUT/processing/LTV_results_AllObs", clear



local graphvars EUW_LTV_FHFA_st EUW_LTV_FHFA_county EUW_LTV_FHFA_tract EUW_or_disaster

graph hbar `graphvars', over(category)  ytitle("Percent Effectively Underwater") legend(label(1 "State") label(2 "County") label(3 "Tract") label(4 "Tract or Disaster")) blabel(bar, format(%9.1f)) graphregion(color(white)) bgcolor(white) //intensity(25)
graph export "$OUTPUT/LTVs_FHFA_All.pdf", replace






























